﻿-- =============================================
-- Author:		Stocksdale, Jon
-- Create date: July 21, 2016
-- Description:	Walk through relationships to create Chain
-- =============================================
CREATE PROCEDURE [dbo].[CreateChain]
	-- Add the parameters for the stored procedure here
	@StartingEntityID nvarchar(100),
	@EndingEntityID nvarchar(100),
	@LEVELS int = 2
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	CREATE TABLE #TempRList (
		E1 nvarchar(100),
		E2 nvarchar(100),
		SRName nvarchar(100),
		Chaintohere nvarchar(max),
		RD int,
		RC int,
		WD int,
		WC int
	)
	CREATE TABLE #Temp (
		E1 nvarchar(100),
		E2 nvarchar(100),
		SRName nvarchar(100),
		Chaintohere nvarchar(max),
		RD int,
		RC int,
		WD int,
		WC int
	)
	CREATE TABLE #Temp2 (
		E1 nvarchar(100),
		E2 nvarchar(100),
		SRName nvarchar(100),
		Chaintohere nvarchar(max),
		RD int,
		RC int,
		WD int,
		WC int
	)
	CREATE TABLE #EXISTINGIDS (
		EntityID nvarchar(100)
	)

	DECLARE @E1Type nvarchar(100), @RC int, @WC int
	DECLARE @E1 nvarchar(100), @E2 nvarchar(100), @SRName nvarchar(100), @Traversed nvarchar(100), @Chaintohere nvarchar(max)
	DECLARE @FinalChain nvarchar(max)
	INSERT INTO #EXISTINGIDS(EntityID) SELECT @StartingEntityID
	SELECT @E1Type=EntityType + COALESCE('.'+EntitySubType,'') From AllEntities WHERE EntityID=@StartingEntityID
	INSERT INTO #TEMP SELECT DISTINCT E1, E2, aer.SRNameWithDir, ';'+aer.SRNameWithDir + ';' + E2 + ';' AS Chaintohere, 
		E1_Read_Direct_E2, E1_Read_Child_E2,
		E1_Write_Direct_E2, E1_Write_Child_E2
		 FROM AllEntityRelationships aer
		 JOIN dbo.GetEntityRelationships(@E1Type,NULL,NULL) ger ON aer.SRNameWithDir = ger.SRNameWithDIr
	 WHERE E1=@StartingEntityID
	SELECT @E2=E2,@FinalChain=Chaintohere FROM #Temp WHERE E2=@EndingEntityID
	IF @E2 = @EndingEntityID 
			SET @LEVELS = 0
	WHILE @LEVELS > 0 BEGIN
		DECLARE RCursor CURSOR FOR
			SELECT DISTINCT E1, E2, SRName, Chaintohere, RC, WC
			FROM #Temp WHERE RC>0
		OPEN RCursor
		FETCH NEXT FROM RCursor INTO @E1, @E2, @SRName, @Chaintohere, @RC, @WC
		WHILE @@FETCH_STATUS=0 AND @LEVELS>0 BEGIN
			SET @TRAVERSED = NULL
			SELECT @TRAVERSED=EntityID FROM #EXISTINGIDS WHERE EntityID=@E2
			IF @TRAVERSED IS NULL BEGIN
				INSERT INTO #TEMP2 SELECT DISTINCT E1, E2, aer.SRNameWithDir, @Chaintohere+aer.SRNameWithDir + ';' + E2 + ';' AS Chaintohere, 
					E1_Read_Direct_E2, E1_Read_Child_E2,
					E1_Write_Direct_E2, E1_Write_Child_E2
					FROM AllEntityRelationships aer
					JOIN dbo.GetEntityRelationships(@E1Type,@RC,@WC) ger ON aer.SRNameWithDir = ger.SRNameWithDIr
					WHERE E1=@E2
				INSERT INTO #EXISTINGIDS(EntityID) SELECT @E2
			END
			SELECT @E2=E2,@Chaintohere=Chaintohere FROM #Temp2 WHERE E2=@EndingEntityID
			IF @E2 = @EndingEntityID BEGIN
				SET @FinalChain = @Chaintohere
				SET @LEVELS = 0
			END
			FETCH NEXT FROM RCursor INTO  @E1, @E2, @SRName, @Chaintohere, @RC, @WC
		END	
		CLOSE RCursor
		DEALLOCATE RCursor
		INSERT INTO #TEMPRLIST SELECT * FROM #Temp
		DELETE FROM #Temp
		INSERT INTO #Temp SELECT * FROM #Temp2
		DELETE FROM #Temp2
		SET @LEVELS = @LEVELS - 1
	END
	SELECT @FinalChain
	INSERT INTO #TEMPRLIST SELECT * FROM #Temp
	SELECT * FROM #TEMPRLIST
	DROP TABLE #Temp
	DROP TABLE #Temp2
	DROP TABLE #TEMPRLIST
	DROP TABLE #ExistingIDS

END